/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.viweb.storemanager.dao;

import com.viweb.storemanager.entity.Category;
import com.viweb.storemanager.entity.Customer;
import com.viweb.storemanager.entity.Employee;
import com.viweb.storemanager.entity.Product;
import com.viweb.storemanager.entity.Rule;
import com.viweb.storemanager.entity.SaleDetail;
import com.viweb.storemanager.entity.SaleInvoice;
import com.viweb.storemanager.entity.ShippingDetail;
import com.viweb.storemanager.entity.ShippingInvoice;
import com.viweb.storemanager.entity.TypeCategory;
import com.viweb.storemanager.entity.TypeCustomer;
import com.viweb.storemanager.entity.Vendor;
import com.viweb.storemanager.entity.WareHouse;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;

/**
 *
 * @author nhchung
 */
public class DAO extends DBConfig {
    //khai báo các thủ tục trong CSDL

    //SYSTEM
    private static String CHECK_LOGIN_SYSTEM = "{CALL CHECK_LOGIN_SYSTEM(?, ?)}";
    private static String CHANGE_PASSWORD_SYSTEM = "{CALL CHANGE_PASSWORD_SYSTEM(?,?,?)}";
    private static String BACKUP_SYSTEM = "{CALL BACKUP_SYSTEM(?, ?, ?)}";
    private static String RESTORE_SYSTEM = "{CALL RESTORE_SYSTEM}";
//EMPLOYEE
    private static String INSERT_EMPLOYEE = "{CALL INSERT_EMPLOYEE(?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_EMPLOYEE = "{CALL DELETE_EMPLOYEE(?)}";
    private static String UPDATE_EMPLOYEE = "{CALL UPDATE_EMPLOYEE(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_EMPLOYEE = "{CALL SELECT_ALL_EMPLOYEE}";
    private static String SELECT_BY_ID_EMPLOYEE = "{CALL SELECT_BY_ID_EMPLOYEE(?)}";
//PRODUCT
    private static String INSERT_PRODUCT = "{CALL INSERT_PRODUCT(?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_PRODUCT = "{CALL DELETE_PRODUCT(?)}";
    private static String UPDATE_PRODUCT = "{CALL UPDATE_PRODUCT(?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_PRODUCT = "{CALL SELECT_ALL_PRODUCT}";
    private static String SELECT_BY_ID_PRODUCT = "{CALL SELECT_BY_ID_PRODUCT(?)}";
//CATEGORY
    private static String INSERT_CATEGORY = "{CALL INSERT_CATEGORY(?, ?, ?, ?, ?)}";
    private static String DELETE_CATEGORY = "{CALL DELETE_CATEGORY(?)}";
    private static String UPDATE_CATEGORY = "{CALL UPDATE_CATEGORY(?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_CATEGORY = "{CALL SELECT_ALL_CATEGORY}";
    private static String SELECT_BY_ID_CATEGORY = "{CALL SELECT_BY_ID_CATEGORY(?)}";
//TYPE
    private static String INSERT_TYPES = "{CALL INSERT_TYPES(?, ?, ?, ?)}";
    private static String DELETE_TYPES = "{CALL DELETE_TYPES(?)}";
    private static String UPDATE_TYPES = "{CALL UPDATE_TYPES(?, ?, ?, ?)}";
    private static String SELECT_ALL_TYPES = "{CALL SELECT_ALL_TYPES}";
    private static String SELECT_BY_ID_TYPES = "{CALL SELECT_BY_ID_TYPES(?)}";
//CUSTOMER
    private static String INSERT_CUSTOMER = "{CALL INSERT_CUSTOMER(?, ?, ?, ?, ?,?,?, ?, ?)}";
    private static String DELETE_CUSTOMER = "{CALL DELETE_CUSTOMER(?)}";
    private static String UPDATE_CUSTOMER = "{CALL UPDATE_CUSTOMER(?, ?, ?, ?, ?,?,?, ?, ?, ?)}";
    private static String SELECT_ALL_CUSTOMER = "{CALL SELECT_ALL_CUSTOMER}";
    private static String SELECT_BY_ID_CUSTOMER = "{CALL SELECT_BY_ID_CUSTOMER(?)}";
//TYPECUSTOMER
    private static String INSERT_TYPE_CUSTOMER = "{CALL INSERT_TYPE_CUSTOMER(?, ?, ?)}";
    private static String DELETE_TYPE_CUSTOMER = "{CALL DELETE_TYPE_CUSTOMER(?)}";
    private static String UPDATE_TYPE_CUSTOMER = "{CALL UPDATE_TYPE_CUSTOMER(?, ?, ?, ?)}";
    private static String SELECT_ALL_TYPE_CUSTOMER = "{CALL SELECT_ALL_TYPE_CUSTOMER}";
    private static String SELECT_BY_ID_TYPE_CUSTOMER = "{CALL SELECT_BY_ID_TYPE_CUSTOMER(?)}";
//MANUFACTURER
    private static String INSERT_VENDOR = "{CALL INSERT_VENDOR(?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_VENDOR = "{CALL DELETE_VENDOR(?)}";
    private static String UPDATE_VENDOR = "{CALL UPDATE_VENDOR(?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_VENDOR = "{CALL SELECT_ALL_VENDOR}";
    private static String SELECT_BY_ID_VENDOR = "{CALL SELECT_BY_ID_VENDOR(?)}";
//SALE_INVOICES
    private static String INSERT_SALE_INVOICES = "{CALL INSERT_SALE_INVOICES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_SALE_INVOICES = "{CALL DELETE_SALE_INVOICES(?)}";
    private static String UPDATE_SALE_INVOICES = "{CALL UPDATE_SALE_INVOICES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_SALE_INVOICES = "{CALL SELECT_ALL_SALE_INVOICES}";
    private static String SELECT_BY_ID_SALE_INVOICES = "{CALL SELECT_BY_ID_SALE_INVOICES(?)}";
//SALE_DETAILS
    private static String INSERT_SALE_DETAILS = "{CALL INSERT_SALE_DETAILS(?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_SALE_DETAILS = "{CALL DELETE_SALE_DETAILS(?, ?)}";
    private static String UPDATE_SALE_DETAILS = "{CALL UPDATE_SALE_DETAILS(?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_SALE_DETAILS = "{CALL SELECT_ALL_SALE_DETAILS}";
    private static String SELECT_BY_ID_SALE_DETAILS = "{CALL SELECT_BY_ID_SALE_DETAILS(?, ?)}";
//SHIPPING_INVOICES
    private static String INSERT_SHIPPING_INVOICES = "{CALL INSERT_SHIPPING_INVOICES(?, ?, ?, ?, ?, ?)}";
    private static String DELETE_SHIPPING_INVOICES = "{CALL DELETE_SHIPPING_INVOICES(?, ?)}";
    private static String UPDATE_SHIPPING_INVOICE = "{CALL UPDATE_SHIPPING_INVOICES(?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_SHIPPING_INVOICES = "{CALL SELECT_ALL_SHIPPING_INVOICES}";
    private static String SELECT_BY_ID_SHIPPING_INVOICES = "{CALL SELECT_BY_ID_SHIPPING_INVOICES(?)}";
//SHIPPING_DETAILS
    private static String INSERT_SHIPPING_DETAILS = "{CALL INSERT_SHIPPING_DETAILS(?, ?, ?, ?, ?, ?, ?)}";
    private static String DELETE_SHIPPING_DETAILS = "{CALL DELETE_SHIPPING_DETAILS(?)}";
    private static String UPDATE_SHIPPING_DETAILS = "{CALL UPDATE_SHIPPING_DETAILS(?, ?, ?, ?, ?, ?, ?)}";
    private static String SELECT_ALL_SHIPPING_DETAILS = "{CALL SELECT_ALL_SHIPPING_DETAILS}";
    private static String SELECT_BY_ID_SHIPPING_DETAILS = "{CALL SELECT_BY_ID_SHIPPING_DETAILS(?)}";
//WAREHOURSE
    private static String INSERT_WAREHOURSE = "{CALL INSERT_WAREHOURSE(?, ?, ?)}";
    private static String DELETE_WAREHOURS = "{CALL DELETE_WAREHOURSE(?)}";
    private static String UPDATE_WAREHOURSE = "{CALL UPDATE_WAREHOURSE(?, ?, ?, ?)}";
    private static String SELECT_ALL_WAREHOURSE = "{CALL SELECT_ALL_WAREHOURSE}";
    private static String SELECT_BY_ID_WAREHOURSE = "{CALL SELECT_BY_ID_WAREHOURSE(?)}";
//RULE
    private static String INSERT_RULE = "{CALL INSERT_RULE(?, ?, ?)}";
    private static String DELETE_RULE = "{CALL DELETE_RULE(?)}";
    private static String UPDATE_RULE = "{CALL UPDATE_RULE(?, ?, ?, ?)}";
    private static String SELECT_ALL_RULE = "{CALL SELECT_ALL_RULE}";
    private static String SELECT_BY_ID_RULE = "{CALL SELECT_BY_ID_RULE(?)}";

    //viết các phương thức truy nhập CSDL
    /****----------BEGIN SYSTEM-----------------***/
    public Employee checkLoginSystem(String username, String password) {
        Employee emp = null;
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.CHECK_LOGIN_SYSTEM);
            cs.setString("username", username);
            cs.setString("password", password);
            ResultSet rs = cs.executeQuery();
            while (rs.next()) {
                emp = new Employee();
                emp.setId(rs.getInt("id"));
                emp.setName(rs.getString("name"));
                emp.setAddress(rs.getString("address"));
                emp.setPhone(rs.getString("phone"));
                emp.setStartdate(new Date(rs.getDate("startdate").getTime()));
                emp.setUsername(rs.getString("username"));
                emp.setPassword(rs.getString("password"));
                emp.setRule(new Rule(rs.getInt("id"), rs.getString("rule")));
                emp.setDatemodifies(new Date(rs.getDate("datemodifies").getTime()));
                emp.setStatus(rs.getByte("status"));
                emp.setNote(rs.getString("note"));
                return emp;
            }
            return null;
        } catch (Exception ex) {
            setError("Login không thành công");
        }
        return null;
    }

    public boolean changePasswordSystem(String username, String oldPassword, String newPassword) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.CHANGE_PASSWORD_SYSTEM);
            cs.setString("username", username);
            cs.setString("oldpassword", oldPassword);
            cs.setString("newpassword", newPassword);
            return cs.executeUpdate() != 0 ? true : false;
        } catch (Exception ex) {
            setError("Đổi mật khẩu không thành công");
        }
        return false;
    }

    public boolean backupSystem(String databaseName, String path, String fileName) {
        //TODO: backup co so du lieu he thong
        return true;
    }

    public boolean restoreSystem() {
        //TODO: phuc hoi he thong
        return true;
    }

    /****----------END SYSTEM-----------------***/
    /****----------BEGIN EMPLOYEEE-----------------***/
    //thêm mới 1 nhân viên
    public boolean insertEmployee(Employee emp) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_EMPLOYEE);
            cs.setString("name", emp.getName());
            cs.setString("address", emp.getAddress());
            cs.setString("phone", emp.getPhone());
            cs.setDate("startdate", new java.sql.Date(emp.getStartdate().getTime()));
            cs.setString("username", emp.getUsername());
            cs.setString("password", emp.getPassword());
            cs.setInt("rule", emp.getRule().getId());
            cs.setInt("status", emp.getStatus());
            cs.setString("note", emp.getNote());
            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError("Không thêm mới được cơ sở dữ liệu");
        }
        return false;
    }

    //cập nhật 1 nhân viên
    public boolean updateEmployee(Employee emp) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_EMPLOYEE);
            cs.setInt("id", emp.getId());
            cs.setString("name", emp.getName());
            cs.setString("address", emp.getAddress());
            cs.setString("phone", emp.getPhone());
            cs.setDate("startdate", new java.sql.Date(emp.getStartdate().getTime()));
            cs.setString("username", emp.getUsername());
            cs.setString("password", emp.getPassword());
            cs.setInt("rule", emp.getRule().getId());
            cs.setInt("status", emp.getStatus());
            cs.setString("note", emp.getNote());
            int result = cs.executeUpdate();
            System.out.println(result);
            if (result != 0) {
                return true;
            } else {
                setError("lệnh không thực hiện được lên dòng nào trong csdl");
            }
        } catch (SQLException ex) {
            setError("Không cập nhật thông tin của nhân viên");
        }
        return false;
    }

    //xoá một nhân viên
    public boolean deleteEmployee(int id) {
        // TODO chuyển thủ tục xóa nhân viên từ chức năng xóa sang chức năng thay stt = 0 => tương tự cho tất cả các thủ tục xóa khác
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_EMPLOYEE);
            cs.setInt(1, id);
            int result = cs.executeUpdate();
            System.out.println(result);
            if (result != 0) {
                return true;
            } else {
                setError("không có nhân viên nào được xóa");
            }
        } catch (SQLException ex) {
            setError("Không xoá được nhân viên");
        }
        return false;
    }

    //lấy tất cả danh sách nhân viên
    public ArrayList<Employee> selectAllEmployee() {
        // TODO nhớ khi select thì chỉ nhận các record mà column status nhận giá trị khác 0

        ArrayList<Employee> list = null;
        try {
            getConnection();
            list = new ArrayList<Employee>();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_EMPLOYEE);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                do {
                    Employee emp = new Employee();
                    emp.setId(rs.getInt("id"));
                    emp.setName(rs.getString("name"));
                    emp.setAddress(rs.getString("address"));
                    emp.setPhone(rs.getString("phone"));
                    emp.setStartdate(new Date(rs.getDate("startdate").getTime()));
                    emp.setUsername(rs.getString("username"));
                    emp.setPassword(rs.getString("password"));
                    emp.setRule(new Rule(rs.getInt("id"), rs.getString("rule")));
                    emp.setDatemodifies(new Date(rs.getDate("datemodifies").getTime()));
                    emp.setStatus(rs.getByte("status"));
                    emp.setNote(rs.getString("note"));
                    list.add(emp);
                } while (rs.next());
                return list;
            }
            return null;
        } catch (Exception ex) {
            return null;
        }
    }

    //lấy về một nhân viên theo id
    public Employee selectByIdEmployee(int id) {
        Employee emp = null;
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_EMPLOYEE);
            cs.setInt("id", id);
            ResultSet rs = cs.executeQuery();
            if (rs.next()) {
                emp = new Employee();
                emp.setId(id);
                emp.setName(rs.getString("name"));
                emp.setAddress(rs.getString("address"));
                emp.setPhone(rs.getString("phone"));
                emp.setStartdate(new Date(rs.getDate("startdate").getTime()));
                emp.setUsername(rs.getString("username"));
                emp.setPassword(rs.getString("password"));
                emp.setRule(new Rule(rs.getInt("id"), rs.getString("rule")));
                emp.setDatemodifies(new Date(rs.getDate("datemodifies").getTime()));
                emp.setStatus(rs.getByte("status"));
                emp.setNote(rs.getString("note"));
                return emp;
            }
            setError("không có nhân viên nào có mã : " + String.valueOf(id));
            return null;
        } catch (Exception ex) {
            return null;
        }
    }

    /****----------END EMPLOYEEE-----------------***/
    /****----------BEGIN PRODUCT-----------------***/
    //chèn 1 sản phẩm vào cơ sở dữ liệu
    public boolean insertProduct(Product product) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_PRODUCT);

            cs.setInt("id", product.getId());
            cs.setString("name", product.getName());
            cs.setInt("number", product.getNumber());
            cs.setInt("warehouse", product.getWarehouse().getId());
            cs.setDouble("price", product.getPrice());
            cs.setString("unit", product.getUnit());
            cs.setInt("category", product.getCategory().getId());
            cs.setByte("status", product.getStatus());
            cs.setString("note", product.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
                // TODO có nên kiểm tra các loại lỗi trả về hay không :
                // 1. mã Product nhập vào đã tồn tại.
                // 2. lỗi khác
                // có nên quản lý những vấn đề này không?
            }
        } catch (SQLException ex) {
            setError("Không thêm mới được sản phẩm!!!");
        }
        return false;
    }

    public boolean updateProduct(Product product) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_PRODUCT);

            cs.setInt("id", product.getId());
            cs.setString("name", product.getName());
            cs.setInt("number", product.getNumber());
            cs.setInt("warehouse", product.getWarehouse().getId());
            cs.setDouble("price", product.getPrice());
            cs.setString("unit", product.getUnit());
            cs.setInt("category", product.getCategory().getId());
            cs.setByte("status", product.getStatus());
            cs.setString("note", product.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError("Không cập nhật được sản phẩm!!!");
        }
        return false;
    }

    public boolean deleteProduct(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_PRODUCT);
            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            } else {
                setError("không có sản phẩm nào bị xóa");
            }
        } catch (SQLException ex) {
            setError("Không thể xóa sản phẩm khỏi danh sách!!!");
        }
        return false;
    }

    public ArrayList<Product> selectAllProduct() {
        ArrayList<Product> productList = null;
        Product product = null;
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_PRODUCT);

            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                productList = new ArrayList<Product>();
                do {
                    product = new Product();

                    product.setId(rs.getInt("id"));
                    product.setName(rs.getString("name"));
                    product.setNumber(rs.getInt("number"));
                    product.setWarehouse(new WareHouse(rs.getInt("warehouse"), rs.getString("whname")));
                    product.setPrice(rs.getDouble("price"));
                    product.setUnit(rs.getString("unit"));
                    product.setCategory(new Category(rs.getInt("category"), rs.getString("ctname")));
                    product.setDatemodifies(rs.getDate("datemodifies"));
                    product.setStatus(rs.getByte("status"));
                    product.setNote(rs.getString("note"));

                    productList.add(product);
                } while (rs.next());
                return productList;
            }
            return null;
        } catch (Exception ex) {
            return null;
        }
    }

    public Product selectByIdProduct(int id) {
        Product product = null;
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_PRODUCT);
            cs.setInt("id", id);

            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                product = new Product();

                product.setId(rs.getInt("id"));
                product.setName(rs.getString("name"));
                product.setNumber(rs.getInt("number"));
                product.setWarehouse(new WareHouse(rs.getInt("warehouse"), rs.getString("whname")));
                product.setPrice(rs.getDouble("price"));
                product.setUnit(rs.getString("unit"));
                product.setCategory(new Category(rs.getInt("category"), rs.getString("ctname")));
                product.setDatemodifies(rs.getDate("datemodifies"));
                product.setStatus(rs.getByte("status"));
                product.setNote(rs.getString("note"));
            }

            return product;
        } catch (Exception ex) {
            return null;
        }
    }

    /****----------END PRODUCT-----------------***/
    /****----------BEGIN WAREHOUSE-----------------***/
    public boolean insertWareHouse(WareHouse warehouse) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_WAREHOURSE);
            cs.setString("name", warehouse.getName());
            cs.setByte("status", warehouse.getStatus());
            cs.setString("note", warehouse.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError(ex.getMessage());
        }
        return false;
    }

    public boolean updateWareHouse(WareHouse warehouse) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_WAREHOURSE);
            cs.setInt("id", warehouse.getId());
            cs.setString("name", warehouse.getName());
            cs.setByte("status", warehouse.getStatus());
            cs.setString("note", warehouse.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            } else {
                setError("không có dòng nào dc chỉnh sửa");
            }
        } catch (SQLException ex) {
            setError(ex.getMessage());
        }
        return false;
    }

    public boolean deleteWareHouse(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_WAREHOURS);
            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            } else {
                setError("không có dòng nào dc xóa");
            }
        } catch (Exception ex) {
            setError(ex.getMessage());
            System.out.println(ex.getMessage());
        }
        return false;
    }

    public ArrayList<WareHouse> selectAllWareHouse() {
        ArrayList<WareHouse> wareList = null;
        WareHouse wareHouse = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_WAREHOURSE);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                wareList = new ArrayList<WareHouse>();
                do {
                    wareHouse = new WareHouse();
                    wareHouse.setId(rs.getInt("id"));
                    wareHouse.setName(rs.getString("name"));
                    wareHouse.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    wareHouse.setStatus(rs.getByte("status"));
                    wareHouse.setNote(rs.getString("note"));
                    wareList.add(wareHouse);
                } while (rs.next());

                return wareList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public WareHouse selectByIdWareHouse(int id) {
        WareHouse wareHouse = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_WAREHOURSE);
            cs.setInt("id", id);

            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                wareHouse = new WareHouse();

                wareHouse.setId(id);
                wareHouse.setName(rs.getString("name"));
                wareHouse.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                wareHouse.setStatus(rs.getByte("status"));
                wareHouse.setNote(rs.getString("note"));

                return wareHouse;
            }

            setError("không có kho nào có mã đó");
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }

        return null;
    }

    /****----------END WAREHOUSE-----------------***/
    /****----------BEGIN RULE-----------------***/
    public boolean insertRule(Rule rule) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_RULE);
            cs.setString("rule", rule.getRule());
            cs.setByte("status", rule.getStatus());
            cs.setString("note", rule.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm quyền mới");
        }
        return false;
    }

    public boolean updateRule(Rule rule) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_RULE);
            cs.setInt("id", rule.getId());
            cs.setString("rule", rule.getRule());
            cs.setByte("status", rule.getStatus());
            cs.setString("note", rule.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật quyền");
        }
        return false;
    }

    public boolean deleteRule(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_RULE);
            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError("Không thể xóa quyền");
        }
        return false;
    }

    public ArrayList<Rule> selectAllRule() {
        ArrayList<Rule> ruleList = null;
        Rule rule = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_RULE);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                ruleList = new ArrayList<Rule>();
                do {
                    rule = new Rule();

                    rule.setId(rs.getInt("id"));
                    rule.setRule(rs.getString("rule"));
                    rule.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    rule.setStatus(rs.getByte("status"));
                    rule.setNote(rs.getString("note"));

                    ruleList.add(rule);
                } while (rs.next());
                setError("csdl trống");
                return ruleList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        setError("csdl trống");
        return null;
    }

    public Rule selectByIdRule(int id) {
        Rule rule = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_RULE);
            cs.setInt("id", id);

            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                rule = new Rule();

                rule.setId(id);
                rule.setRule(rs.getString("rule"));
                rule.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                rule.setStatus(rs.getByte("status"));
                rule.setNote(rs.getString("note"));

                return rule;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END RULE-----------------***/
    /****----------BEGIN CATEGOTY-----------------***/
    public boolean insertCategory(Category category) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_CATEGORY);

            cs.setInt("id", category.getId());
            cs.setString("name", category.getName());
            cs.setInt("type", category.getType().getId());
            cs.setByte("status", category.getStatus());
            cs.setString("note", category.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm loại hàng mới");
        }
        return false;
    }

    public boolean updateCategory(Category category) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_CATEGORY);
            cs.setInt("id", category.getId());
            cs.setString("name", category.getName());
            cs.setInt("type", category.getType().getId());
            cs.setByte("status", category.getStatus());
            cs.setString("note", category.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin loại hàng");
        }
        return false;
    }

    public boolean deleteCategory(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_CATEGORY);
            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError("Không thể xóa quyền");
        }
        return false;
    }

    public ArrayList<Category> selectAllCategory() {
        ArrayList<Category> categoryList = null;
        Category category = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_CATEGORY);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                categoryList = new ArrayList<Category>();
                do {
                    category = new Category();

                    category.setId(rs.getInt("id"));
                    category.setName(rs.getString("name"));
                    category.setType(new TypeCategory(rs.getInt("type"), rs.getString("typename")));
                    category.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    category.setStatus(rs.getByte("status"));
                    category.setNote(rs.getString("note"));

                    categoryList.add(category);
                } while (rs.next());

                return categoryList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public Category selectByIdCategory(int id) {
        Category category = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_CATEGORY);
            cs.setInt("id", id);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                category = new Category();

                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setType(new TypeCategory(rs.getInt("type"), rs.getString("typename")));
                category.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                category.setStatus(rs.getByte("status"));
                category.setNote(rs.getString("note"));

                return category;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END CATEGOTY-----------------***/
    /****----------BEGIN TYPE CATEGORY-----------------***/
    public boolean insertTypeCategory(TypeCategory typeCategory) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_TYPES);

            cs.setInt("id", typeCategory.getId());
            cs.setString("name", typeCategory.getName());
            cs.setByte("status", typeCategory.getStatus());
            cs.setString("note", typeCategory.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm ngành hàng mới");
        }
        return false;
    }

    public boolean updateTypeCategory(TypeCategory typeCategory) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_TYPES);

            cs.setInt("id", typeCategory.getId());
            cs.setString("name", typeCategory.getName());
            cs.setByte("status", typeCategory.getStatus());
            cs.setString("note", typeCategory.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin ngành hàng");
        }
        return false;
    }

    public boolean deleteTypeCategory(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_TYPES);
            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (SQLException ex) {
            setError("Không thể xóa ngành hàng");
        }
        return false;
    }

    public ArrayList<TypeCategory> selectAllTypeCategory() {
        ArrayList<TypeCategory> typeCategoryList = null;
        TypeCategory typeCategory = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_TYPES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                typeCategoryList = new ArrayList<TypeCategory>();
                do {
                    typeCategory = new TypeCategory();

                    typeCategory.setId(rs.getInt("id"));
                    typeCategory.setName(rs.getString("name"));
                    typeCategory.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    typeCategory.setStatus(rs.getByte("status"));
                    typeCategory.setNote(rs.getString("note"));

                    typeCategoryList.add(typeCategory);
                } while (rs.next());
                return typeCategoryList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public TypeCategory selectByIdTypeCategory(int id) {
        TypeCategory typeCategory = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_TYPES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                typeCategory = new TypeCategory();

                typeCategory.setId(rs.getInt("id"));
                typeCategory.setName(rs.getString("name"));
                typeCategory.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                typeCategory.setStatus(rs.getByte("status"));
                typeCategory.setNote(rs.getString("note"));

                return typeCategory;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END TYPE CATEGOTY-----------------***/
    /****----------BEGIN VENDOR-----------------***/
    public boolean insertVendor(Vendor vendor) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_VENDOR);

            cs.setInt("id", vendor.getId());
            cs.setString("name", vendor.getName());
            cs.setString("address", vendor.getAddress());
            cs.setString("phone", vendor.getPhone());
            cs.setString("fax", vendor.getFax());
            cs.setString("email", vendor.getEmail());
            cs.setByte("status", vendor.getStatus());
            cs.setString("note", vendor.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm kho mới");
        }
        return false;
    }

    public boolean updateVendor(Vendor vendor) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_VENDOR);

            cs.setInt("id", vendor.getId());
            cs.setString("name", vendor.getName());
            cs.setString("address", vendor.getAddress());
            cs.setString("phone", vendor.getPhone());
            cs.setString("fax", vendor.getFax());
            cs.setString("email", vendor.getEmail());
            cs.setByte("status", vendor.getStatus());
            cs.setString("note", vendor.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin Kho");
        }
        return false;
    }

    public boolean deleteVendor(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_VENDOR);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa thông tin Kho");
        }
        return false;
    }

    public ArrayList<Vendor> selectAllVendor() {
        ArrayList<Vendor> vendorList = null;
        Vendor vendor = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_VENDOR);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                vendorList = new ArrayList<Vendor>();
                do {
                    vendor = new Vendor();

                    vendor.setId(rs.getInt("id"));
                    vendor.setName(rs.getString("name"));
                    vendor.setAddress(rs.getString("address"));
                    vendor.setPhone(rs.getString("phone"));
                    vendor.setFax(rs.getString("fax"));
                    vendor.setEmail(rs.getString("email"));
                    vendor.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    vendor.setStatus(rs.getByte("status"));
                    vendor.setNote(rs.getString("note"));

                    vendorList.add(vendor);
                } while (rs.next());
                return vendorList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public Vendor selectByIdVendor(int id) {
        Vendor vendor = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_VENDOR);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                vendor = new Vendor();

                vendor.setId(rs.getInt("id"));
                vendor.setName(rs.getString("name"));
                vendor.setAddress(rs.getString("address"));
                vendor.setPhone(rs.getString("phone"));
                vendor.setFax(rs.getString("fax"));
                vendor.setEmail(rs.getString("email"));
                vendor.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                vendor.setStatus(rs.getByte("status"));
                vendor.setNote(rs.getString("note"));

                return vendor;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END VENDOR-----------------***/
    /****----------BEGIN CUSTOMER-----------------***/
    public boolean insertCustomer(Customer customer) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_CUSTOMER);

            cs.setString("name", customer.getName());
            cs.setInt("type", customer.getType().getId());
            cs.setString("address", customer.getAddress());
            cs.setString("phone", customer.getPhone());
            cs.setString("email", customer.getEmail());
            cs.setString("accountnumber", customer.getAccountNumber());
            cs.setString("bank", customer.getBank());
            cs.setByte("status", customer.getStatus());
            cs.setString("note", customer.getNote());
            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm khách hàng mới");
        }
        return false;
    }

    public boolean updateCustomer(Customer customer) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_CUSTOMER);

            cs.setInt("id", customer.getId());
            cs.setString("name", customer.getName());
            cs.setInt("type", customer.getType().getId());
            cs.setString("address", customer.getAddress());
            cs.setString("phone", customer.getPhone());
            cs.setString("email", customer.getEmail());
            cs.setString("accountnumber", customer.getAccountNumber());
            cs.setString("bank", customer.getBank());
            cs.setByte("status", customer.getStatus());
            cs.setString("note", customer.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin khách hàng mới");
        }
        return false;
    }

    public boolean deleteCustomer(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_CUSTOMER);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa thông tin khách hàng mới");
        }
        return false;
    }

    public ArrayList<Customer> selectAllCustomer() {
        ArrayList<Customer> customerList = null;
        Customer customer = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_CUSTOMER);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                customerList = new ArrayList<Customer>();
                do {
                    customer = new Customer();

                    customer.setId(rs.getInt("id"));
                    customer.setName(rs.getString("name"));
                    customer.setType(new TypeCustomer(rs.getInt("type"), rs.getString("typename")));
                    customer.setAddress(rs.getString("address"));
                    customer.setPhone(rs.getString("phone"));
                    customer.setEmail(rs.getString("email"));
                    customer.setAccountNumber(rs.getString("accountnumber"));
                    customer.setBank(rs.getString("bank"));
                    customer.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    customer.setStatus(rs.getByte("status"));
                    customer.setNote(rs.getString("note"));

                    customerList.add(customer);
                } while (rs.next());
                return customerList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public Customer selectByIdCustomer(int id) {
        Customer customer = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_CUSTOMER);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                customer = new Customer();

                customer.setId(id);
                customer.setName(rs.getString("name"));
                customer.setType(new TypeCustomer(rs.getInt("type"), rs.getString("typename")));
                customer.setAddress(rs.getString("address"));
                customer.setPhone(rs.getString("phone"));
                customer.setEmail(rs.getString("email"));
                customer.setAccountNumber(rs.getString("accountnumber"));
                customer.setBank(rs.getString("bank"));
                customer.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                customer.setStatus(rs.getByte("status"));
                customer.setNote(rs.getString("note"));

                return customer;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END CUSTOMER-----------------***/
    /****----------BEGIN TYPE CUSTOMER-----------------***/
    public boolean insertTypeCustomer(TypeCustomer typeCustomer) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_TYPE_CUSTOMER);

            cs.setString("name", typeCustomer.getName());
            cs.setByte("status", typeCustomer.getStatus());
            cs.setString("note", typeCustomer.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm khách hàng mới");
        }
        return false;
    }

    public boolean updateTypeCustomer(TypeCustomer typeCustomer) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_TYPE_CUSTOMER);

            cs.setInt("id", typeCustomer.getId());
            cs.setString("name", typeCustomer.getName());
            cs.setByte("status", typeCustomer.getStatus());
            cs.setString("note", typeCustomer.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin khách hàng mới");
        }
        return false;
    }

    public boolean deleteTypeCustomer(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_TYPE_CUSTOMER);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa thông tin khách hàng mới");
        }
        return false;
    }

    public ArrayList<TypeCustomer> selectAllTypeCustomer() {
        ArrayList<TypeCustomer> customerList = null;
        TypeCustomer typeCustomer = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_TYPE_CUSTOMER);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                customerList = new ArrayList<TypeCustomer>();
                do {
                    typeCustomer = new TypeCustomer();

                    typeCustomer.setId(rs.getInt("id"));
                    typeCustomer.setName(rs.getString("name"));
                    typeCustomer.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    typeCustomer.setStatus(rs.getByte("status"));
                    typeCustomer.setNote(rs.getString("note"));

                    customerList.add(typeCustomer);
                } while (rs.next());
                return customerList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public Customer selectByIdTypeCustomer(int id) {
        Customer customer = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_TYPE_CUSTOMER);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                customer = new Customer();

                customer.setId(id);
                customer.setName(rs.getString("name"));
                customer.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                customer.setStatus(rs.getByte("status"));
                customer.setNote(rs.getString("note"));

                return customer;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END TYPE CUSTOMER-----------------***/
    /****----------BEGIN SHIPPING INVOICE-----------------***/
    public boolean insertShippingInvoice(ShippingInvoice shippingInvoice) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_SHIPPING_INVOICES);

            cs.setInt("vendor", shippingInvoice.getVendor().getId());
            cs.setDate("date", new java.sql.Date(shippingInvoice.getDate().getTime()));
            cs.setInt("employee", shippingInvoice.getEmployee().getId());
            cs.setBoolean("isbillable", shippingInvoice.isIsBillable());
            cs.setByte("status", shippingInvoice.getStatus());
            cs.setString("note", shippingInvoice.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm hóa đơn nhập hàng mới");
        }
        return false;
    }

    public boolean updateShippingInvoice(ShippingInvoice shippingInvoice) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_SHIPPING_INVOICE);

            cs.setInt("id", shippingInvoice.getId());
            cs.setInt("vendor", shippingInvoice.getVendor().getId());
            cs.setDate("date", new java.sql.Date(shippingInvoice.getDate().getTime()));
            cs.setInt("employee", shippingInvoice.getEmployee().getId());
            cs.setBoolean("isbillable", shippingInvoice.isIsBillable());
            cs.setByte("status", shippingInvoice.getStatus());
            cs.setString("note", shippingInvoice.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin hóa đơn");
        }
        return false;
    }

    public boolean deleteShippingInvoice(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_SHIPPING_INVOICES);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa hóa đơn");
        }
        return false;
    }

    public ArrayList<ShippingInvoice> selectAllShippingInvoice() {
        ArrayList<ShippingInvoice> shippingInvoiceList = null;
        ShippingInvoice shippingInvoice = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_SHIPPING_INVOICES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                shippingInvoiceList = new ArrayList<ShippingInvoice>();
                do {
                    shippingInvoice = new ShippingInvoice();

                    shippingInvoice.setId(rs.getInt("id"));
                    shippingInvoice.setVendor(new Vendor(rs.getInt("verdor"), rs.getString("vendorname")));
                    shippingInvoice.setDate(new java.sql.Date(rs.getDate("date").getTime()));
                    shippingInvoice.setEmployee(new Employee(rs.getInt("employee"), rs.getString("empname")));
                    shippingInvoice.setIsBillable(rs.getBoolean("isbillable"));
                    shippingInvoice.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    shippingInvoice.setStatus(rs.getByte("status"));
                    shippingInvoice.setNote(rs.getString("note"));

                    shippingInvoiceList.add(shippingInvoice);
                } while (rs.next());
                return shippingInvoiceList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public ShippingInvoice selectByIdShippingInvoice(int id) {
        ShippingInvoice shippingInvoice = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_SHIPPING_INVOICES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                shippingInvoice = new ShippingInvoice();

                shippingInvoice.setId(id);
                shippingInvoice.setVendor(new Vendor(rs.getInt("verdor"), rs.getString("vendorName")));
                shippingInvoice.setDate(new java.sql.Date(rs.getDate("date").getTime()));
                shippingInvoice.setEmployee(new Employee(rs.getInt("employee"), rs.getString("empName")));
                shippingInvoice.setIsBillable(rs.getBoolean("isbillable"));
                shippingInvoice.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                shippingInvoice.setStatus(rs.getByte("status"));
                shippingInvoice.setNote(rs.getString("note"));

                return shippingInvoice;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END SHIPPING INVOICE-----------------***/
    /****----------BEGIN SALE INVOICE-----------------***/
    public boolean insertSaleInvoice(SaleInvoice saleInvoice) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_SALE_INVOICES);

            cs.setInt("customer", saleInvoice.getCustomer().getId());
            cs.setDate("date", new java.sql.Date(saleInvoice.getDate().getTime()));
            cs.setInt("employee", saleInvoice.getEmployee().getId());
            cs.setString("buyer", saleInvoice.getBuyer());
            cs.setDouble("paid", saleInvoice.getPaid());
            cs.setString("tranportation", saleInvoice.getTranportation());
            cs.setString("tranportphone", saleInvoice.getTranportPhone());
            cs.setDouble("tranportcost", saleInvoice.getTranportcost());
            cs.setBoolean("isbillable", saleInvoice.isIsbillable());
            cs.setByte("status", saleInvoice.getStatus());
            cs.setString("note", saleInvoice.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm hóa đơn bán hàng");
        }
        return false;
    }

    public boolean updateSaleInvoice(SaleInvoice saleInvoice) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_SALE_INVOICES);

            cs.setInt("id", saleInvoice.getId());
            cs.setInt("customer", saleInvoice.getCustomer().getId());
            cs.setDate("date", new java.sql.Date(saleInvoice.getDate().getTime()));
            cs.setInt("employee", saleInvoice.getEmployee().getId());
            cs.setString("buyer", saleInvoice.getBuyer());
            cs.setDouble("paid", saleInvoice.getPaid());
            cs.setString("tranportation", saleInvoice.getTranportation());
            cs.setString("tranportphone", saleInvoice.getTranportPhone());
            cs.setDouble("tranportcost", saleInvoice.getTranportcost());
            cs.setBoolean("isbillable", saleInvoice.isIsbillable());
            cs.setByte("status", saleInvoice.getStatus());
            cs.setString("note", saleInvoice.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật hóa đơn bán hàng");
        }
        return false;
    }

    public boolean deleteSaleInvoice(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_SALE_INVOICES);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm hóa đơn nhập hàng mới");
        }
        return false;
    }

    public ArrayList<SaleInvoice> selectAllSaleInvoice() {
        ArrayList<SaleInvoice> saleInvoiceList = null;
        SaleInvoice saleInvoice = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_SALE_INVOICES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                saleInvoiceList = new ArrayList<SaleInvoice>();
                do {
                    saleInvoice = new SaleInvoice();
                    saleInvoice.setId(rs.getInt("id"));
                    saleInvoice.setCustomer(new Customer(rs.getInt("customer"), rs.getString("cusName")));
                    saleInvoice.setDate(new java.sql.Date(rs.getDate("date").getTime()));
                    saleInvoice.setEmployee(new Employee(rs.getInt("employee"), rs.getString("empName")));
                    saleInvoice.setBuyer(rs.getString("buyer"));
                    saleInvoice.setPaid(rs.getDouble("paid"));
                    saleInvoice.setTranportation(rs.getString("tranportation"));
                    saleInvoice.setTranportPhone(rs.getString("tranportphone"));
                    saleInvoice.setTranportcost(rs.getDouble("tranportcost"));
                    saleInvoice.setIsbillable(rs.getBoolean("isbillable"));
                    saleInvoice.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    saleInvoice.setStatus(rs.getByte("status"));
                    saleInvoice.setNote(rs.getString("note"));

                    saleInvoiceList.add(saleInvoice);
                } while (rs.next());
                return saleInvoiceList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public SaleInvoice selectByIdSaleInvoice(int id) {
        SaleInvoice saleInvoice = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_SALE_INVOICES);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                saleInvoice = new SaleInvoice();

                saleInvoice.setId(rs.getInt("id"));
                saleInvoice.setCustomer(new Customer(rs.getInt("customer"), rs.getString("cusName")));
                saleInvoice.setDate(new java.sql.Date(rs.getDate("date").getTime()));
                saleInvoice.setEmployee(new Employee(rs.getInt("employee"), rs.getString("empName")));
                saleInvoice.setBuyer(rs.getString("buyer"));
                saleInvoice.setPaid(rs.getDouble("paid"));
                saleInvoice.setTranportation(rs.getString("tranportation"));
                saleInvoice.setTranportPhone(rs.getString("tranportphone"));
                saleInvoice.setTranportcost(rs.getDouble("tranportcost"));
                saleInvoice.setIsbillable(rs.getBoolean("isbillable"));
                saleInvoice.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                saleInvoice.setStatus(rs.getByte("status"));
                saleInvoice.setNote(rs.getString("note"));

                return saleInvoice;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END SALE INVOICE-----------------***/
    /****----------BEGIN SALE DETAILS-----------------***/
    public boolean insertSaleDetail(SaleDetail saleDetail) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_SALE_DETAILS);

            cs.setInt("id", saleDetail.getId());
            cs.setInt("invoice", saleDetail.getInvoice().getId());
            cs.setInt("product", saleDetail.getProduct().getId());
            cs.setInt("number", saleDetail.getNumber());
            cs.setDouble("price", saleDetail.getPrice());
            cs.setString("unit", saleDetail.getUnit());
            cs.setByte("status", saleDetail.getStatus());
            cs.setString("note", saleDetail.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm sản phẩm vào hóa đơn bán hàng");
        }
        return false;
    }

    public boolean updateSaleDetail(SaleDetail saleDetail) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_SALE_DETAILS);

            cs.setInt("id", saleDetail.getId());
            cs.setInt("invoice", saleDetail.getInvoice().getId());
            cs.setInt("product", saleDetail.getProduct().getId());
            cs.setInt("number", saleDetail.getNumber());
            cs.setDouble("price", saleDetail.getPrice());
            cs.setString("unit", saleDetail.getUnit());
            cs.setByte("status", saleDetail.getStatus());
            cs.setString("note", saleDetail.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin sản phẩm trong hóa đơn bán hàng");
        }
        return false;
    }

    public boolean deleteSaleDetail(int id) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_SALE_DETAILS);

            cs.setInt("id", id);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa thông tin sản phẩm trong hóa đơn bán hàng");
        }
        return false;
    }

    public ArrayList<SaleDetail> selectAllSaleDetail() {
        ArrayList<SaleDetail> saleDetailList = null;
        SaleDetail saleDetails = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_SALE_DETAILS);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                saleDetailList = new ArrayList<SaleDetail>();
                do {
                    saleDetails = new SaleDetail();

                    saleDetails.setId(rs.getInt("id"));
                    saleDetails.setInvoice(new SaleInvoice(rs.getInt("invoice")));
                    saleDetails.setProduct(new Product(rs.getInt("product"), rs.getString("productName")));
                    saleDetails.setNumber(rs.getInt("number"));
                    saleDetails.setPrice(rs.getDouble("price"));
                    saleDetails.setUnit(rs.getString("unit"));
                    saleDetails.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    saleDetails.setStatus(rs.getByte("status"));
                    saleDetails.setNote(rs.getString("note"));

                    saleDetailList.add(saleDetails);
                } while (rs.next());
                return saleDetailList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public SaleDetail selectByIdSaleDetail(int id) {
        SaleDetail saleDetails = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_SALE_DETAILS);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                saleDetails = new SaleDetail();

                saleDetails.setId(rs.getInt("id"));
                saleDetails.setInvoice(new SaleInvoice(rs.getInt("invoice")));
                saleDetails.setProduct(new Product(rs.getInt("product"), rs.getString("productName")));
                saleDetails.setNumber(rs.getInt("number"));
                saleDetails.setPrice(rs.getDouble("price"));
                saleDetails.setUnit(rs.getString("unit"));
                saleDetails.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                saleDetails.setStatus(rs.getByte("status"));
                saleDetails.setNote(rs.getString("note"));

                return saleDetails;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    /****----------END SALE DETAILS------------------***/
    /****----------BEGIN SHIPPING DETAILS-----------------***/
    public boolean insertShippingDetail(ShippingDetail shippingDetail) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.INSERT_SHIPPING_DETAILS);

            cs.setInt("invoice", shippingDetail.getInvoice().getId());
            cs.setInt("product", shippingDetail.getProduct().getId());
            cs.setInt("number", shippingDetail.getNumber());
            cs.setDouble("price", shippingDetail.getPrice());
            cs.setString("unit", shippingDetail.getUnit());
            cs.setByte("status", shippingDetail.getStatus());
            cs.setString("note", shippingDetail.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể thêm sản phẩm vào hóa đơn bán hàng");
        }
        return false;
    }

    public boolean updateShippingDetail(ShippingDetail shippingDetail) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.UPDATE_SHIPPING_DETAILS);

            cs.setInt("invoice", shippingDetail.getInvoice().getId());
            cs.setInt("product", shippingDetail.getProduct().getId());
            cs.setInt("number", shippingDetail.getNumber());
            cs.setDouble("price", shippingDetail.getPrice());
            cs.setString("unit", shippingDetail.getUnit());
            cs.setByte("status", shippingDetail.getStatus());
            cs.setString("note", shippingDetail.getNote());

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể cập nhật thông tin sản phẩm trong hóa đơn bán hàng");
        }
        return false;
    }

    public boolean deleteShippingDetail(int invoice, String product) {
        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.DELETE_SHIPPING_DETAILS);

            cs.setInt("invoice", invoice);
            cs.setString("product", product);

            if (cs.executeUpdate() != 0) {
                return true;
            }
        } catch (Exception ex) {
            setError("Không thể xóa thông tin sản phẩm trong hóa đơn bán hàng");
        }
        return false;
    }

    public ArrayList<ShippingDetail> selectAllShippingDetail() {
        ArrayList<ShippingDetail> shippingDetailList = null;
        ShippingDetail shippingDetails = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_ALL_SHIPPING_DETAILS);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                shippingDetailList = new ArrayList<ShippingDetail>();
                do {
                    shippingDetails = new ShippingDetail();

                    shippingDetails.setInvoice(new ShippingInvoice(rs.getInt("invoice")));
                    shippingDetails.setProduct(new Product(rs.getInt("product"), rs.getString("productName")));
                    shippingDetails.setNumber(rs.getInt("number"));
                    shippingDetails.setPrice(rs.getDouble("price"));
                    shippingDetails.setUnit(rs.getString("unit"));
                    shippingDetails.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                    shippingDetails.setStatus(rs.getByte("status"));
                    shippingDetails.setNote(rs.getString("note"));

                    shippingDetailList.add(shippingDetails);
                } while (rs.next());
                return shippingDetailList;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }

    public ShippingDetail selectByIdShippingDetail(int id) {
        ShippingDetail shippingDetails = null;

        try {
            getConnection();
            CallableStatement cs = con.prepareCall(DAO.SELECT_BY_ID_SHIPPING_DETAILS);
            ResultSet rs = cs.executeQuery();

            if (rs.next()) {
                shippingDetails = new ShippingDetail();

                shippingDetails.setId(rs.getInt("id"));
                shippingDetails.setInvoice(new ShippingInvoice(rs.getInt("invoice")));
                shippingDetails.setProduct(new Product(rs.getInt("product"), rs.getString("productName")));
                shippingDetails.setNumber(rs.getInt("number"));
                shippingDetails.setPrice(rs.getDouble("price"));
                shippingDetails.setUnit(rs.getString("unit"));
                shippingDetails.setDatemodifies(new java.sql.Date(rs.getDate("datemodifies").getTime()));
                shippingDetails.setStatus(rs.getByte("status"));
                shippingDetails.setNote(rs.getString("note"));

                return shippingDetails;
            }
        } catch (Exception ex) {
            setError("Không thể đọc dữ liệu");
        }
        return null;
    }
    /****----------END SHIPPING DETAILS------------------***/
}
